package com.orhon.smartcampus.modules.systemctl.dao;

import com.orhon.smartcampus.modules.systemctl.entity.Modules;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.transform.Transformers;
import org.springframework.stereotype.Component;

import com.orhon.smartcampus.basicComponents.baseDao.BaseDaoImpl;
import com.orhon.smartcampus.modules.systemctl.entity.Menus;

import javax.transaction.Transactional;
import java.util.List;

@Component
public class MenusDao extends BaseDaoImpl<Menus> {

    /**
     * 根据用户id查询分配的菜单
     * @param userid
     * @return
     */
    @Transactional
    public List findBySQL(Long userid) {
        StringBuffer sb = new StringBuffer();
        sb.append(" SELECT nu.*  FROM  systemctl_operation_menu menu  ");
        sb.append(" LEFT JOIN systemctl_menus nu on menu.menu_id=nu.id");
        sb.append(" WHERE menu.operation_id in");
        sb.append(" ( SELECT sodr.operation_id ");
        sb.append(" FROM systemctl_operation_department_relation AS sodr ");
        sb.append(" JOIN systemctl_operation_user_relation AS sour ");
        sb.append(" WHERE  sodr.dep_duty IN ( SELECT DISTINCT sodur.dep_duty FROM systemctl_org_department_user_relation sodur WHERE sodur.user_id = :userid )");
        sb.append(" UNION ( SELECT sour.operation_id FROM systemctl_operation_user_relation AS sour WHERE sour.user_id=:userid )) and nu.id !=0 ORDER BY ordered ASC");
        Session session = this.getSession();
        Query query = session.createSQLQuery(sb.toString()).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).setParameter("userid",userid);
        return query.list();
    }


    /**
     * 获取学校的所有菜单列表
     * @param school_id
     * @return
     */
    @Transactional
    public List findSchoolAdminmenus(Long school_id) {
        StringBuffer sb = new StringBuffer();
        sb.append(
                    "SELECT menus.* " +
                    "FROM " +
                    "systemctl_menus menus " +
                    "JOIN " +
                    "(SELECT DISTINCT sm.id FROM  systemctl_modules_school_relations school_modules   " +
                    "LEFT JOIN " +
                    "systemctl_modules sm on school_modules.module_id=sm.id " +
                    "WHERE school_modules.school_id = :school_id and sm.id !=0 ) as md " +
                    "ON " +
                    "menus.module_id = md.id " +
                    "WHERE  menus.id !=0 AND ISNULL(menus.deleted_at) AND menus.status=1 " +
                    "ORDER BY menus.ordered ASC"
                );

        Session session = this.getSession();
        Query query = session.createSQLQuery(sb.toString()).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).setParameter("school_id",school_id);
        return query.list();
    }

    /**
     * 获取超级管理员的菜单
     * @param userid
     * @return
     */
    @Transactional
    public List findSuperAdminmenus(Long userid ) {
        StringBuffer sb = new StringBuffer();
        sb.append(
                "SELECT menus.* " +
                        "FROM systemctl_menus menus " +
                        "WHERE  menus.id !=0 AND ISNULL(menus.deleted_at) AND menus.status=1 " +
                        "ORDER BY menus.ordered ASC"
        );

        Session session = this.getSession();
        Query query = session.createSQLQuery(sb.toString()).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
        return query.list();
    }
    /**
     * 查询给用户分配的模块列表
     * @param userid
     * @return
     */
    @Transactional
    public List<Modules> findModuleByUserID(Long userid) {
        StringBuffer sqlString = new StringBuffer();
        sqlString.append(
                " SELECT smd.*,u_m.* " +
                        "FROM ( " +
                            "SELECT DISTINCT nu.id AS menu_id, nu.module_id " +
                            "FROM systemctl_operation_menu menu " +
                            "LEFT JOIN systemctl_menus nu ON menu.menu_id = nu.id " +
                            "WHERE  menu.operation_id IN ( " +
                                "SELECT sodr.operation_id " +
                                "FROM systemctl_operation_department_relation AS sodr " +
                                "JOIN systemctl_operation_user_relation AS sour  " +
                                "WHERE sodr.dep_duty IN ( " +
                                    "SELECT DISTINCT sodur.dep_duty AS dep_duty " +
                                    "FROM systemctl_org_department_user_relation AS sodur " +
                                    "WHERE sodur.user_id = :userid ) " +
                                    "UNION " +
                                    "SELECT sour.operation_id " +
                                    "FROM systemctl_operation_user_relation AS sour " +
                                    "WHERE sour.user_id = :userid  ) " +
                            "AND nu.id != 0 " +
                        ") " +
                        "AS u_m " +
                        "LEFT JOIN systemctl_modules AS smd " +
                        "ON u_m.menu_id = smd.home_id " +
                        "WHERE smd.id != 0 and ISNULL(smd.deleted_at)");
        Session session = this.getSession();
        Query query = session.createSQLQuery(sqlString.toString()).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).setParameter("userid",userid);
        return query.list();
    }
    // 获取学校管理
    @Transactional
    public List<Modules> findSchoolAdminModules(Long school_id) {
        StringBuffer sqlString = new StringBuffer();
        sqlString.append(
                "SELECT DISTINCT sm.*  FROM  systemctl_modules_school_relations school_modules " +
                        " LEFT JOIN systemctl_modules sm on school_modules.module_id=sm.id " +
                        "WHERE school_id = :school_id and sm.id !=0 and ISNULL(sm.deleted_at) ORDER BY module_order ASC");
        Session session = this.getSession();
        Query query = session.createSQLQuery(sqlString.toString()).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).setParameter("school_id",school_id);
        return query.list();
    }

    /**
     * 获取超级管理员的模块列表
     * @param userid
     * @return
     */
    @Transactional
    public List<Modules> findSuperAdminModules(Long userid) {
        StringBuffer sqlString = new StringBuffer();
        sqlString.append(
                "SELECT DISTINCT sm.*  FROM systemctl_modules sm " +
                        "WHERE sm.status=1 and ISNULL(sm.deleted_at) ORDER BY module_order ASC");
        Session session = this.getSession();
        Query query = session.createSQLQuery(sqlString.toString()).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
        return query.list();
    }
}
